package demo4.mapper;

import demo4.pojo.UserInfo;
import demo4.util.IDBUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Component
public class UserMapper implements IUserMapper {
    @Autowired
    private IDBUtil dbUtil;

    @Override
    public int insert(UserInfo userInfo) {
        System.out.println("UserMapper:插入User表");
        /*
        1.获取数据库连接
        2.创建Connection对象
        3.创建PreparedStatement对象
        4.执行SQL语句
        5.接收数据库处理结果
        6.关闭数据库链接
        7.返回结果
         */
        try (Connection connection = dbUtil.getConnection();) {
            String sql = "insert into userinfo(username,password,nickname,age) values(?,?,?,?)";
            PreparedStatement ps = connection.prepareStatement(sql); //  创建PreparedStatement对象
            ps.setString(1, userInfo.getUsername());
            ps.setString(2, userInfo.getPassword());
            ps.setString(3, userInfo.getNickname());
            ps.setInt(4, userInfo.getAge());
            return ps.executeUpdate(); //  返回结果

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public UserInfo selectByUsername(String username) throws SQLException {
        System.out.println("UserMapper:查询User表");
        /*
            1:链接数据库
            2:执行查询语句
              SELECT id,username,password,nickname,age
              FROM userinfo
              WHERE username=?
            3:执行得到一个查询结果集
            4:将结果集中查询出来的该用户信息封装成UserInfo对象
              例如:
              if(rs.next()){
                Userinfo userinfo = new Userinfo();
                //把查询到的这条记录中个字段的值设置到userinfo对象的对应属性上
                userinfo.setUsername(rs.getString("username"))
                ...
                return userinfo;
              }else{
                return null;//没有查询到记录
              }
         */
        try (Connection conn = dbUtil.getConnection();){
            String sql = "SELECT id,username,password,nickname,age FROM userinfo WHERE username=?";
            PreparedStatement ps = conn.prepareStatement(sql);
            ps.setString(1, username);

            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt("id"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setNickname(rs.getString("nickname"));
                userInfo.setAge(rs.getInt("age"));
                return userInfo;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<UserInfo> selectAll(){
        System.out.println("UserMapper:查询User表");
        List<UserInfo> userlist = new ArrayList<>() ;
        try (Connection conn = dbUtil.getConnection();){
            String sql = "SELECT id,username,password,nickname,age FROM userinfo";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()) {
                UserInfo userInfo = new UserInfo();
                userInfo.setId(rs.getInt("id"));
                userInfo.setUsername(rs.getString("username"));
                userInfo.setPassword(rs.getString("password"));
                userInfo.setNickname(rs.getString("nickname"));
                userInfo.setAge(rs.getInt("age"));
                userlist.add(userInfo);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }

        return userlist;
    }
}
